import cron from 'node-cron';
import db from './mysqlHelper.js';
import logger from './logger.js';
import moment from 'moment';


console.log('已加载定时任务');
//每小进执行一次
cron.schedule('0 * * * *', () => {
    console.log('每小时执行的任务');
    // 在这里添加你想要执行的任务代码
    updateNoParkBoard();
    //
    //检查时间是否为26日0点 自动创建新的订单表
    autoCreateOrderInfoTable();
    updatePosBiclyeType();

});
// async function autoCreateOrderInfoTable() {
//     //检查时间是否为26日0点
//     let now = moment();
//     if (now.date() === 26 && now.hour() === 0) {
//         //创新新的库
//         now =now.add(1,'months');
//         let newDbName = `orderInfo_${now.format('YYYYMM')}`;
//         let sql = `CREATE TABLE ${newDbName} LIKE orderInfo`;
//         let res =await db.query(sql);
//         if (res instanceof Error) {
//             logger.error(`autoCreateOrderInfoTable error :` + res.message);
//             return;
//         }

//     }
// }
async function autoCreateOrderInfoTable() {
    // 检查时间是否为周一0点
    let now = moment();
    if (now.day() === 1 && now.hour() === 0) {
        // 计算下周的时间
        let nextWeek = now.add(1, 'weeks');
        let newDbName = `orderInfo_${nextWeek.format('YYYYWW')}`;
        let sql = `CREATE TABLE ${newDbName} LIKE orderInfo`;
        console.log('auto created table:', newDbName);
        let res = await db.query(sql);
        if (res instanceof Error) {
            logger.error(`autoCreateOrderInfoTable error :` + res.message);
            return;
        }
    }
}
async function updateNoParkBoard() {
    let noParkData = {
        id: "1"
    }
    //统计车辆信息
    let sql = `select COUNT(*) as total_vehicles, 
    COUNT(CASE WHEN bicycleType = 2 THEN 1 END) AS electric_bikes,
    COUNT(CASE WHEN bicycleType = 1 THEN 1 END) AS bicycles
    from bicycle`;
    let vehicleData = await db.query(sql);
    if (vehicleData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + vehicleData.message);
        return;
    }
    if (vehicleData.length > 0) {
        vehicleData = vehicleData[0];
        noParkData.bikeCount = vehicleData.total_vehicles;
        noParkData.electricBicycleCount = vehicleData.electric_bikes;
        noParkData.bicycleCount = vehicleData.bicycles;
    }
    sql = `select COUNT(*) as count from electronicFence`;
    let electronicFenceData = await db.query(sql);
    if (electronicFenceData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + electronicFenceData.message);
        return;
    }
    if (electronicFenceData.length > 0) {
        noParkData.fenceCount = electronicFenceData[0].count;
    }
    sql = `select COUNT(*) as count from noParkArea`;
    let noParkAreaData = await db.query(sql);
    if (noParkAreaData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + noParkAreaData.message);
        return;
    }
    if (noParkAreaData.length > 0) {
        noParkData.noParkCount = noParkAreaData[0].count;
    }
    //按查询昨天的超投巡查记录总数
    //let yesterday = moment().subtract(1, 'days');
    let yesterday = moment();
    let yesterdayStr = yesterday.format('YYYY-MM-DD');
    sql = `select COUNT(*) as count from inspectionRecord where date(createTime)='${yesterdayStr}'`;
    let inspectionRecordData = await db.query(sql);
    if (inspectionRecordData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + inspectionRecordData.message);
        return;
    }
    if (inspectionRecordData.length > 0) {
        noParkData.InspectionFrequency = inspectionRecordData[0].count;
    }
    //接入企业数量
    sql = `select COUNT(*) as count from enterprise`;
    let enterpriseData = await db.query(sql);
    if (enterpriseData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + enterpriseData.message);
        return;
    }
    if (enterpriseData.length > 0) {
        noParkData.enterpiseCount = enterpriseData[0].count;
    }
    let tableName = `orderInfo_${yesterday.format('YYYYWW')}`;
    //从订单信息表中查询昨天的订单总数 订单总里程 订单总金额
    sql = `select COUNT(*) as count, SUM(mileage)/1000 as totalDistance, 
    SUM(totalCost) as totalAmount from ${tableName} where date(endTime)='${yesterdayStr}'`;
    let orderInfoData = await db.query(sql);
    if (orderInfoData instanceof Error) {
        logger.error(`updateNoParkBoard error :` + orderInfoData.message);
        return;
    }
    if (orderInfoData.length > 0) {
        noParkData.userCount = orderInfoData[0].count;
        noParkData.orderCount = orderInfoData[0].count;
        noParkData.dayMileage = orderInfoData[0].totalDistance;
        noParkData.dayAmount = orderInfoData[0].totalAmount;
    }
    noParkData.complaint = 0;

    //检查记录是否存
    let isExist = await db.checkExist('noParkBoard', { id: noParkData.id });
    if (isExist) {
        let res = await db.update('noParkBoard', noParkData, { id: noParkData.id });
        if (res instanceof Error) {
            logger.error(`update noParkBoard error :` + res.message);
            return;
        }
    } else {
        let res = await db.create('noParkBoard', noParkData);
        if (res instanceof Error) {
            logger.error(`insert noParkBoard error :` + res.message);
            return;
        }
    }
    // console.log(noParkData);


}

async function updatePosBiclyeType() {
    let sql = `
    UPDATE bicyclepos bp
    JOIN bicycle b ON bp.bicycleCode = b.bicycleCode
    SET bp.bicycleType = b.bicycleType;
`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`Update bicycleType error: ` + res.message);
    }
    else {
        logger.info(`Update bicycleType success`);
    }
}

export default {
    updateNoParkBoard
}
